OSC Kontoauszug bereinigen

1. Mietverhältnisse bereinigen (erst 2. Teil des scriptes ausfühern, dann ersten. Beides in OM auf dem Mandanten)

2. Mietverhältnis part 2

3. Mietverhältnisse nochmals bereinigen

4. Sync ausführen

5. OBjekt & Jahr von Fehler in Script eintragen (ReadKontoauszug3ForUpdate):

DECLARE @jahr int = 2018
DECLARE @obnr int = 5046
DECLARE @BUGUID varchar(36)

select
IBNSY_JAHR
,IBNSY_TYPUS
,IBNSY_BUSYMBOL
,IBNSY_BEZEICHNUNG
into #tmpSy
from dbo.CPI_BNSY sy with(nolock)
where sy.IBNSY_JAHR >= @jahr
AND sy.IBNSY_TEXTKZ = 0

select
CASE IBNBU_SYMBOL
WHEN 98 THEN 1
WHEN 99 THEN 1
ELSE 2
END AS Sortierung
,bu.IBNBU_GUID ,bu.IBNBU_OBNR ,bu.IBNBU_BNNR ,bu.IBNBU_JAHR ,bu.IBNBU_BELEGDAT ,bu.IBNBU_SYMBOL ,bu.IBNBU_BELEGNR ,bu.IBNBU_BRUTTOBETRAG
,bu.IBNBU_TEXTNR
,bu.IBNBU_PRZ

into #tmp
from dbo.CPI_BNBUCH bu with(nolock)
WHERE bu.IBNBU_OBNR = @obnr AND bu.IBNBU_JAHR >= @jahr

/*select
Sortierung
,bu.IBNBU_GUID AS KA_GUID
,bu.IBNBU_OBNR AS KA_OBNR
,bu.IBNBU_BNNR AS KA_BNNR
,bu.IBNBU_JAHR AS KA_JAHR
,bu.IBNBU_BELEGDAT AS KA_BELEGDAT
,bu.IBNBU_SYMBOL AS KA_SYMBOL
,bu.IBNBU_BELEGNR AS KA_BELEGNR
,(bu.IBNBU_BRUTTOBETRAG * -1) AS KA_BRUTTOBETRAG
,bu.IBNBU_TEXTNR AS KA_TEXTNR
,pez.EH_ID AS EH_ID
,pez.PEI_ID AS PEI_ID
,pez.PE_ID AS PE_ID
,eh.OB_ID AS OB_ID
,sy.IBNSY_BEZEICHNUNG AS KA_SYMBOL_TEXT
,bu.IBNBU_PRZ AS KA_PRZ
,sy.IBNSY_BEZEICHNUNG + RTRIM(' ' + ISNULL(butxt.IBNBT_BUCHUNGSTEXT, '')) AS KA_BUCHUNGSTEXT
*/
select @BUGUID = bu.IBNBU_GUID
from #tmp bu

INNER JOIN dbo.NET_PER_EINHEIT_ZU pez with(nolock)
ON pez.PEI_OBJEKT = bu.IBNBU_OBNR
AND pez.PEI_BNNUMME = bu.IBNBU_BNNR
AND pez.PEI_ABGLEICH < 10
AND
(
YEAR(pez.PEI_AUSZUG_DATUM) >= @jahr
OR
pez.PEI_AUSZUG_DATUM is null
)

inner join NET_PERSON pe with(nolock)
on pe.PE_ID = pez.PE_ID

INNER JOIN CPI_PERSONVERKNUEPFUNG pev with(nolock)
ON pev.IPV_KEY1 = bu.IBNBU_OBNR
and pev.IPV_KEY2 = bu.IBNBU_BNNR
and pev.IPV_KEY3 = bu.IBNBU_PRZ
and pev.IPV_JAHR = bu.IBNBU_JAHR
and pev.IPV_PID = pe.PE_JET_NR
and pev.IPV_TYP = 1

INNER JOIN dbo.NET_EINHEIT eh with(nolock)
ON eh.EH_ID = pez.EH_ID

INNER JOIN dbo.CPI_BNSTAMM st with(nolock)
ON st.IBNST_OBNR = bu.IBNBU_OBNR
AND IBNST_BNNR = IBNBU_BNNR
AND IBNST_PRZ = IBNBU_PRZ
AND IBNST_JAHR = IBNBU_JAHR

INNER JOIN #tmpSy sy with(nolock)
ON sy.IBNSY_JAHR = IBNBU_JAHR
AND sy.IBNSY_TYPUS = st.IBNST_IBNSY_TYPUS
AND sy.IBNSY_BUSYMBOL = bu.IBNBU_SYMBOL

LEFT OUTER JOIN dbo.CPI_BNBUTXT butxt with(nolock)
on bu.IBNBU_OBNR = butxt.IBNBT_OBNR
AND bu.IBNBU_BNNR = butxt.IBNBT_BNNR
AND bu.IBNBU_PRZ = butxt.IBNBT_PRZ
AND bu.IBNBU_JAHR = butxt.IBNBT_JAHR
AND bu.IBNBU_TEXTNR = butxt.IBNBT_LFDNR
group by IBNBU_GUID
having count(*) > 1

select
Sortierung
,bu.IBNBU_GUID AS KA_GUID
,bu.IBNBU_OBNR AS KA_OBNR
,bu.IBNBU_BNNR AS KA_BNNR
,bu.IBNBU_JAHR AS KA_JAHR
,bu.IBNBU_BELEGDAT AS KA_BELEGDAT
,bu.IBNBU_SYMBOL AS KA_SYMBOL
,bu.IBNBU_BELEGNR AS KA_BELEGNR
,(bu.IBNBU_BRUTTOBETRAG * -1) AS KA_BRUTTOBETRAG
,bu.IBNBU_TEXTNR AS KA_TEXTNR
,pez.EH_ID AS EH_ID
,pez.PEI_ID AS PEI_ID
,pez.PE_ID AS PE_ID
,eh.OB_ID AS OB_ID
,sy.IBNSY_BEZEICHNUNG AS KA_SYMBOL_TEXT
,bu.IBNBU_PRZ AS KA_PRZ
,sy.IBNSY_BEZEICHNUNG + RTRIM(' ' + ISNULL(butxt.IBNBT_BUCHUNGSTEXT, '')) AS KA_BUCHUNGSTEXT

from #tmp bu

INNER JOIN dbo.NET_PER_EINHEIT_ZU pez with(nolock)
ON pez.PEI_OBJEKT = bu.IBNBU_OBNR
AND pez.PEI_BNNUMME = bu.IBNBU_BNNR
AND pez.PEI_ABGLEICH < 10
AND
(
YEAR(pez.PEI_AUSZUG_DATUM) >= @jahr
OR
pez.PEI_AUSZUG_DATUM is null
)

inner join NET_PERSON pe with(nolock)
on pe.PE_ID = pez.PE_ID

INNER JOIN CPI_PERSONVERKNUEPFUNG pev with(nolock)
ON pev.IPV_KEY1 = bu.IBNBU_OBNR
and pev.IPV_KEY2 = bu.IBNBU_BNNR
and pev.IPV_KEY3 = bu.IBNBU_PRZ
and pev.IPV_JAHR = bu.IBNBU_JAHR
and pev.IPV_PID = pe.PE_JET_NR
and pev.IPV_TYP = 1

INNER JOIN dbo.NET_EINHEIT eh with(nolock)
ON eh.EH_ID = pez.EH_ID

INNER JOIN dbo.CPI_BNSTAMM st with(nolock)
ON st.IBNST_OBNR = bu.IBNBU_OBNR
AND IBNST_BNNR = IBNBU_BNNR
AND IBNST_PRZ = IBNBU_PRZ
AND IBNST_JAHR = IBNBU_JAHR

INNER JOIN #tmpSy sy with(nolock)
ON sy.IBNSY_JAHR = IBNBU_JAHR
AND sy.IBNSY_TYPUS = st.IBNST_IBNSY_TYPUS
AND sy.IBNSY_BUSYMBOL = bu.IBNBU_SYMBOL

LEFT OUTER JOIN dbo.CPI_BNBUTXT butxt with(nolock)
on bu.IBNBU_OBNR = butxt.IBNBT_OBNR
AND bu.IBNBU_BNNR = butxt.IBNBT_BNNR
AND bu.IBNBU_PRZ = butxt.IBNBT_PRZ
AND bu.IBNBU_JAHR = butxt.IBNBT_JAHR
AND bu.IBNBU_TEXTNR = butxt.IBNBT_LFDNR

where IBNBU_GUID = @BUGUID

drop table #tmp
drop table #tmpSy

/*
update NET_PER_EINHEIT_ZU
set PEI_ABGLEICH = 10
where pei_id = ''
*/